from database import get_db_pool


class NewsDataRepository(object):
    def __init__(self):
        self.db_conn = get_db_pool()
        self.db_conn = self.db_conn.connection()

    def __del__(self):
        self.db_conn.close()

    def select(self, item, table):
        if item.pageSize > 100:
            return {
                "total_count": 0,
                "num_pages": 0,
                "data": 0
            }
        # 连接 MySQL 数据库
        cursor = self.db_conn.cursor()
        # 动态拼接 SQL 查询语句
        query = f"SELECT id,title,publish_time,introduce,source,link,create_time FROM {table} WHERE state=1 "

        if item.project:
            query += f"AND source = '{item.project}' "
        if item.start_time:
            query += f"AND create_time >= '{item.start_time}' "
        if item.end_time:
            query += f"AND create_time <= '{item.end_time}' "
        if item.keyword:
            query += f"AND title LIKE '%{item.keyword}%'"
        page_query = query.replace("id,title,publish_time,introduce,source,link,create_time", "COUNT(*)")
        # 添加 LIMIT 子句来限制结果数量
        query += f" ORDER BY create_time DESC LIMIT {item.pageSize} OFFSET {item.pageSize * (item.pageNum - 1)}"
        # 执行查询
        cursor.execute(query)
        # 获取查询结果
        results = cursor.fetchall()
        # 获取总数
        cursor.execute(page_query)
        total_count = cursor.fetchone()["COUNT(*)"]
        # 计算分页相关参数
        num_pages = (total_count + item.pageSize - 1) // item.pageSize
        # 关闭数据库连接
        cursor.close()
        return {
            "total_count": total_count,
            "num_pages": num_pages,
            "data": results
        }

    def project_select(self, table):
        """
        项目列表查询
        """
        # 连接 MySQL 数据库
        cursor = self.db_conn.cursor()
        query = f"SELECT DISTINCT source FROM {table}"
        # 执行查询
        cursor.execute(query)
        # 获取查询结果
        results = cursor.fetchall()
        return [result["source"] for result in results]

    def detail_select(self, table, data_id):
        """
        详情查询
        """
        # 连接 MySQL 数据库
        cursor = self.db_conn.cursor()
        query = f"SELECT id,title,publish_time,introduce,source,link,content,html_content,create_time FROM {table} where id={data_id}"
        # 执行查询
        cursor.execute(query)
        # 获取查询结果
        results = cursor.fetchall()
        return results[0]

    def insert_oss_data(self, table, file_data):
        # 连接 MySQL 数据库
        cursor = self.db_conn.cursor()
        keys = ', '.join(file_data.keys())
        values = ', '.join(['%s'] * len(file_data))
        query = f'INSERT INTO {table} ({keys}) VALUES ({values});'
        # 执行
        cursor.execute(query)
        # 获取结果
        result = cursor.fetchall()
        return result

    def select_oss_data(self, item, table):
        cursor = self.db_conn.cursor()
        query = f"SELECT * FROM {table} where 1=1"
        if item.keyword:
            query += f"AND file_name LIKE '%{item.keyword}%'"
        page_query = query.replace("*", "COUNT(*)")
        # 添加 LIMIT 子句来限制结果数量
        query += f" LIMIT {item.pageSize} OFFSET {item.pageSize * (item.pageNum - 1)}"
        # 执行查询
        cursor.execute(query)
        # 获取查询结果
        results = cursor.fetchall()
        # 获取总数
        cursor.execute(page_query)
        total_count = cursor.fetchone()["COUNT(*)"]
        # 计算分页相关参数
        num_pages = (total_count + item.pageSize - 1) // item.pageSize
        # 关闭数据库连接
        cursor.close()
        return {
            "total_count": total_count,
            "num_pages": num_pages,
            "data": results
        }
